package com.diy.sigmund.diycommon.test.controller.excel.easyexcel;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.FileUtils;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.diy.sigmund.diycommon.test.controller.excel.easyexcel.register.CustomCellWriteHandler;
import com.diy.sigmund.diycommon.test.controller.excel.easyexcel.register.CustomSheetWriteHandler;
import com.diy.sigmund.diycommon.test.controller.excel.easyexcel.register.CustomerDailyImportListener;
import com.diy.sigmund.diycommon.test.controller.excel.easyexcel.register.StringConverter;
import com.diy.sigmund.diycommon.test.entity.FormDataDTO;
import com.diy.sigmund.diycommon.util.json.JacksonUtil;
import com.google.common.collect.Lists;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RequestPart;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

/**
 * <br>https://mp.weixin.qq.com/s/Mt8_EE0NsH-cSUdJb5b8Xg
 * <br>https://blog.csdn.net/wuzhiwei549/article/details/105874226
 * <br>https://blog.csdn.net/qq_32258777/article/details/89031479
 * <br>https://blog.csdn.net/jianggujin/article/details/80200400
 *
 * @author ylm-sigmund
 * @since 2021/9/14 17:23
 */
@RestController
public class ExcelExportController {

    Logger log = LoggerFactory.getLogger(ExcelExportController.class);

    @PostMapping("/import1")
    public void importCustomerDaily1(FormDataDTO formDataDTO,
            @RequestPart("file") MultipartFile file) throws IOException {
        // @RequestPart-同时上传文件和json的解决方案
        // 1、postman Body类型选择form-data，选择文件类型上传文件，其它选择text
        // key value
        // name 张小凡
        // phone 119

        // 2、jmeter在HTTP request页面勾选Use multipart/form-data for POST，同时在header删除Content-type
        // 在Files Upload 标签页MIME Type属性填application/octet-stream
        // 在同层级的Parameters填对应的属性键值对
        // name 张小凡
        // phone 119
        log.info("formDataDTO:{}", JacksonUtil.toJson(formDataDTO));
        InputStream inputStream = file.getInputStream();
        List<ReqCustomerDailyImport> reqCustomerDailyImports = EasyExcel.read(inputStream)
                .head(ReqCustomerDailyImport.class)
                // 设置sheet,默认读取第一个
                .sheet()
                // 设置标题所在行数
                .headRowNumber(2)
                .doReadSync();
        reqCustomerDailyImports.forEach(o -> log.info(JacksonUtil.toJson(o)));
    }

    @PostMapping("/import")
    public void importCustomerDaily(@RequestParam MultipartFile file)
            throws IOException {

        InputStream inputStream = file.getInputStream();
        List<ReqCustomerDailyImport> reqCustomerDailyImports = EasyExcel.read(inputStream)
                .head(ReqCustomerDailyImport.class)
                // 设置sheet,默认读取第一个
                .sheet()
                // 设置标题所在行数
                .headRowNumber(2)
                .doReadSync();
        reqCustomerDailyImports.forEach(o -> log.info(JacksonUtil.toJson(o)));
    }

    @PostMapping("/import/verify")
    public void importCustomerDailyVerify(@RequestParam MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();
        List<ReqCustomerDailyImport> reqCustomerDailyImports = EasyExcel.read(inputStream)
                // 这个转换是成全局的， 所有java为string,excel为string的都会用这个转换器。
                // 如果就想单个字段使用请使用@ExcelProperty 指定converter
                .registerConverter(new StringConverter())
                // 注册监听器，可以在这里校验字段
                .registerReadListener(new CustomerDailyImportListener())
                .head(ReqCustomerDailyImport.class)
                .sheet()
                .headRowNumber(2)
                .autoTrim(true)
                .doReadSync();
        reqCustomerDailyImports.forEach(o -> log.info(JacksonUtil.toJson(o)));
    }

    @PostMapping("/importBig")
    public void importBig(@RequestParam MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();
        // 20万数据，同步解析，用了5分钟。本质也是Sax read
        List<ReqCustomerDailyImport> reqCustomerDailyImports = EasyExcel.read(inputStream)
                // 这个转换是成全局的， 所有java为string,excel为string的都会用这个转换器。
                // 如果就想单个字段使用请使用@ExcelProperty 指定converter
                .registerConverter(new StringConverter())
                // 注册监听器，可以在这里校验字段
                .registerReadListener(new CustomerDailyImportListener())
                .head(ReqCustomerDailyImport.class)
                .sheet()
                .headRowNumber(2)
                .doReadSync();
        // Sax read，也用了5分钟，不注册自定义的SyncReadListener，无法拿到数据。
        // EasyExcel.read(inputStream, ReqCustomerDailyImport.class, new CustomerDailyImportListener())
        //         .registerConverter(new StringConverter())
        //         .sheet(0)
        //         .headRowNumber(2)
        //         .doRead();
        log.info("解析完成");
    }

    /**
     * https://blog.csdn.net/wuzhiwei549/article/details/105874226
     * 读多个或者全部sheet,这里注意一个sheet不能读取多次，多次读取需要重新读取文件
     * 1. 创建excel对应的实体对象
     * 2. 由于默认异步读取excel，所以需要创建excel一行一行的回调监听器
     * 3. 直接读即可
     */
    public void repeatedRead() {
        String fileName = "demo" + File.separator + "demo.xlsx";
        // 读取全部sheet
        // 这里需要注意 ReqCustomerDailyImportListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个ReqCustomerDailyImportListener里面写
        EasyExcel.read(fileName, ReqCustomerDailyImport.class, new CustomerDailyImportListener()).doReadAll();
        // 读取部分sheet
        fileName = "demo" + File.separator + "demo.xlsx";
        ExcelReader excelReader = EasyExcel.read(fileName).build();
        // 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
        ReadSheet readSheet1 =
                EasyExcel.readSheet(0).head(ReqCustomerDailyImport.class)
                        .registerReadListener(new CustomerDailyImportListener()).build();
        ReadSheet readSheet2 =
                EasyExcel.readSheet(1).head(ReqCustomerDailyImport.class)
                        .registerReadListener(new CustomerDailyImportListener()).build();
        // 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
        excelReader.read(readSheet1, readSheet2);
        // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
        excelReader.finish();
    }

    /**
     * 同步的返回，不推荐使用，如果数据量大会把数据放到内存里面
     */
    public void synchronousRead() {
        String fileName = "demo" + File.separator + "demo.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 同步读取会自动finish
        List<Object> list = EasyExcel.read(fileName).head(ReqCustomerDailyImport.class).sheet().doReadSync();
        for (Object obj : list) {
            ReqCustomerDailyImport data = (ReqCustomerDailyImport) obj;
            log.info("读取到数据:{}", JacksonUtil.toJson(data));
        }
        // 这里 也可以不指定class，返回一个list，然后读取第一个sheet 同步读取会自动finish
        list = EasyExcel.read(fileName).sheet().doReadSync();
        for (Object obj : list) {
            // 返回每条数据的键值对 表示所在的列 和所在列的值
            Map<Integer, String> data = (Map<Integer, String>) obj;
            log.info("读取到数据:{}", JacksonUtil.toJson(data));
        }
    }

    /**
     * 大数据量导出
     */
    @GetMapping("/export")
    public void export(HttpServletResponse response) throws IOException {
        // 生成数据
        // List<RespCustomerDailyExport> respCustomerDailyExports = getRespCustomerDailyExports(200000);
        List<RespCustomerDailyExport> respCustomerDailyExports = getRespCustomerDailyExports(10);

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("testExport", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        // 此电脑下载
        EasyExcel.write(response.getOutputStream(), RespCustomerDailyExport.class)
                .sheet("sheet0")
                // 设置字段宽度为自动调整，不太精确
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .doWrite(respCustomerDailyExports);
    }

    @GetMapping("/export1")
    public void export1(HttpServletResponse response) throws IOException, ClassNotFoundException {
        List<RespCustomerDailyExport> list = getRespCustomerDailyExports(10);

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        EasyExcel.write(os, RespCustomerDailyExport.class)
                .sheet("sheet1")
                .doWrite(list);

        ByteArrayInputStream inputStream = new ByteArrayInputStream(os.toByteArray());

        // 导出和导入的对象定义是不一致的
        List<ReqCustomerDailyImport> reqCustomerDailyImports = EasyExcel.read(inputStream)
                .head(ReqCustomerDailyImport.class)
                .sheet()
                .headRowNumber(1)
                .doReadSync();
        System.out.println("size:" + reqCustomerDailyImports.size());

    }
    @GetMapping("/hutoolExport")
    public void hutoolExport(HttpServletResponse response) throws IOException, ClassNotFoundException {
        List<RespCustomerDailyExport> list = getRespCustomerDailyExports(10);

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        EasyExcel.write(os, RespCustomerDailyExport.class)
                .sheet("sheet1")
                .doWrite(list);

        ByteArrayInputStream inputStream = new ByteArrayInputStream(os.toByteArray());
        // ObjectInputStream objectInputStream = new ObjectInputStream(inputStream);
        // Object object = objectInputStream.readObject();
        List<List<Object>> read = ExcelUtil.getReader(inputStream).read();

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("hutoolExport", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        ExcelWriter writer = ExcelUtil.getWriter(true);
        writer.write(read);
        ServletOutputStream outputStream = response.getOutputStream();
        writer.flush(outputStream,true);
        writer.close();
        IoUtil.close(outputStream);
    }

    private List<RespCustomerDailyExport> getRespCustomerDailyExports(int count) {
        List<RespCustomerDailyExport> respCustomerDailyImports = Lists.newArrayList();
        for (int i = 0; i < count; i++) {
            RespCustomerDailyExport respCustomerDailyExport = new RespCustomerDailyExport();
            respCustomerDailyExport.setCustomerName("  ss  customerName小明-" + i);
            respCustomerDailyExport.setMisCode(i + "  s ");
            respCustomerDailyExport.setMonthlyQuota(new BigDecimal(String.valueOf(i)));
            respCustomerDailyExport.setAccountReceivableQuota(new BigDecimal(String.valueOf(i)));
            respCustomerDailyExport.setDailyInterestRate(new BigDecimal(String.valueOf(i)));
            respCustomerDailyImports.add(respCustomerDailyExport);
        }
        return respCustomerDailyImports;
    }

    /**
     * 图片导出
     */
    @GetMapping("/export/imageWrite")
    public void imageWrite(HttpServletResponse response) {
        String fileName = "imageWrite" + System.currentTimeMillis() + ".xlsx";
        String imagePath = ExcelExportController.class.getResource("/static/game/fight/1.jpg").getPath();
        try (InputStream inputStream = FileUtils.openInputStream(new File(imagePath))) {
            List<ImageData> list = new ArrayList<ImageData>();
            ImageData imageData = new ImageData();
            list.add(imageData);

            // 放入四种类型的图片 实际使用只要选一种即可
            imageData.setByteArray(FileUtils.readFileToByteArray(new File(imagePath)));
            imageData.setFile(new File(imagePath));
            imageData.setString(imagePath);

            imageData.setInputStream(inputStream);
            // 文件路径：项目根目录下
            // EasyExcel.write(fileName, ImageData.class).sheet().doWrite(list);
            // 文件路径：此电脑 | 下载
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), ImageData.class)
                    .sheet("sheet0")
                    .doWrite(list);
        } catch (Exception e) {
            log.error("", e);
        }
    }

    /**
     * 自定义样式
     * 实现会比较复杂，需要做头策略，内容策略，字体大小等
     */
    @GetMapping("/export/customStyleWrite")
    public void customStyleWrite(HttpServletResponse response) throws IOException {
        String fileName = "styleWrite" + System.currentTimeMillis() + ".xlsx";
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 20);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 20);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        // 这里 需要指定写用哪个class去读，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // EasyExcel.write(fileName, RespCustomerDailyExport.class).registerWriteHandler(horizontalCellStyleStrategy)
        //         .sheet("模板")
        //         .doWrite(getRespCustomerDailyExports(10));
        // 文件路径：此电脑 | 下载
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), RespCustomerDailyExport.class)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .sheet("sheet0")
                .doWrite(getRespCustomerDailyExports(10));
    }

    /** 下拉，超链接 */
    @GetMapping("/export/customHandlerWrite")
    public void customHandlerWrite(HttpServletResponse response) throws IOException {
        String fileName = "customHandlerWrite" + System.currentTimeMillis() + ".xlsx";
        // 文件路径：此电脑 | 下载
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), RespCustomerDailyExport.class)
                .registerWriteHandler(new CustomSheetWriteHandler())
                .registerWriteHandler(new CustomCellWriteHandler())
                .sheet("模板")
                .doWrite(getRespCustomerDailyExports(5));
    }

    /** 单元格合并 */
    @GetMapping("/export/mergeWrite")
    public void mergeWrite(HttpServletResponse response) throws IOException {
        String fileName = "mergeWrite" + System.currentTimeMillis() + ".xlsx";
        // 每隔2行会合并。当然其他合并策略也可以自己写
        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
        // 文件路径：此电脑 | 下载
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        // 这里 需要指定写用哪个class去读，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(response.getOutputStream(), RespCustomerDailyExport.class)
                .registerWriteHandler(loopMergeStrategy)
                .sheet("模板")
                .doWrite(getRespCustomerDailyExports(5));
    }
}
